Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports EntityLayer

Public Class SolicitudRequerimientoDA

    Public Shared Function ObtenerNuevoCodigo() As String

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("COM_SolicitudRequerimiento_GenClave", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim para As SqlParameter

        para = New SqlParameter("@strClaveGenerada", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(para)

        myConnection.Open()

        myCommand.ExecuteNonQuery()

        myConnection.Close()

        Return myCommand.Parameters("@strClaveGenerada").Value

    End Function

    Public Shared Function Insertar(ByVal OBE As SolicitudRequerimientoBE) As Boolean
        Try
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("COM_SolicitudRequerimiento_Add", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@strNumeroSolicitud", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Numero
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@dtFechaEmision", SqlDbType.DateTime)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.FechaEmision
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strNombreRequerimiento", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.NombreRequerimiento
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strTipoRequerimiento", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.TipoRequerimiento
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strPrioridad", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Prioridad
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strJustificacion", SqlDbType.VarChar, 200)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Justificacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strObservacion", SqlDbType.VarChar, 200)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Observacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@bolAprobacion", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Aprobacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strCodigoSolicitante", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.CodigoSolicitante
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True

        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try

    End Function

    Public Shared Function Actualizar(ByVal OBE As SolicitudRequerimientoBE) As Boolean
        Try
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("COM_SolicitudRequerimiento_Upd", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@strNumeroSolicitud", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Numero
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@dtFechaEmision", SqlDbType.DateTime)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.FechaEmision
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strNombreRequerimiento", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.NombreRequerimiento
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strTipoRequerimiento", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.TipoRequerimiento
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strPrioridad", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Prioridad
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strJustificacion", SqlDbType.VarChar, 200)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Justificacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strObservacion", SqlDbType.VarChar, 200)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Observacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@bolAprobacion", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.Aprobacion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strCodigoSolicitante", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = OBE.CodigoSolicitante
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True

        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try

    End Function

    Public Shared Function Eliminar(ByVal strNroSolicitud As String) As Boolean
        Try
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("COM_SolicitudRequerimiento_Del", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@strNumeroSolicitud", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = strNroSolicitud
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True
        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try

    End Function

    Public Shared Function ListarTodo() As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_SolicitudRequerimiento_ListAll", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        myDataadapter.Fill(ds, "SolicitudRequerimiento")

        Return ds

    End Function

    Public Shared Function ListarxCodigo(ByVal strNumeroSolicitud As String) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_SolicitudRequerimiento_ListByCodigo", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@strNumeroSolicitud", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = strNumeroSolicitud
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "SolicitudRequerimiento")

        Return ds

    End Function

    Public Shared Function ListarTodoxEstado(ByVal bolEstado As Boolean) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_SolicitudRequerimiento_ListAllByEstado", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@bolEstado", SqlDbType.Bit)
        para.Direction = ParameterDirection.Input
        para.Value = bolEstado
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "SolicitudPedido")

        Return ds

    End Function

    Public Shared Function ListarxFechas(ByVal strFechaInicio As DateTime, ByVal strFechaFin As DateTime) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_SolicitudRequerimiento_ListAllByFechas", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@strFechaInicio", SqlDbType.DateTime)
        para.Direction = ParameterDirection.Input
        para.Value = strFechaInicio
        myDataadapter.SelectCommand.Parameters.Add(para)

        para = New SqlParameter("@strFechaFin", SqlDbType.DateTime)
        para.Direction = ParameterDirection.Input
        para.Value = strFechaFin
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "SolicitudRequerimiento")

        Return ds

    End Function
End Class
